import pymysql
from string import Template
#创建数据库连接

connect = pymysql.connect(host='81.70.27.234',port=3306,user='root',password='010825lwj',db='filmInfo',connect_timeout=28800,max_allowed_packet=32)
  
#创建游标
cursor = connect.cursor()
#创建表,用于存储电影详情数据，字段有电影名，评分，导演，主演，国家，以及上映时间，时间我用时间戳来存储
def create_detail_table(year:int):
    tablename = str(year) + "_detail"
    create_sql = Template(""" create table ${tablename}(
        name varchar(50) primary key,
        grade float,
        types varchar(100) not null,
        actors varchar(100),
        director varchar(50),
        countries varchar(100) null,
        mins int,
        releaseDate bigint(20)        
        )
    """)
    drop_sql = Template("DROP TABLE IF EXISTS ${tablename}")
    #判断是否已有该表，有则删除
    cursor.execute(drop_sql.substitute(tablename=tablename))
    #创建表
    cursor.execute(create_sql.substitute(tablename=tablename))
#添加电影详情数据
def add_detail(formate_data:dict,year:int):
    #插入语句
    add_sql = Template(""" insert into ${year}_detail(
        name,grade,types,actors,director,countries,mins,releaseDate
    ) values
    (
       '${name}',${grade},'${types}','${actors}','${director}',
       '${countries}',${mins},${releaseDate}
    )
    """)
    formate_data['year'] = str(year)
    #执行语句
    #有可能断开连接
    cursor.execute(add_sql.substitute(formate_data))
            #提交操作到数据库
    connect.commit()
